"""
企业级金融数据MCP服务系统 - 股票数据访问层
提供股票相关数据的专用访问功能
"""

from typing import List, Optional, Dict, Any, Union
from datetime import datetime, date, timedelta
from decimal import Decimal
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, and_, or_, desc, asc, func
from sqlalchemy.sql import Select

from src.dao.base import FinancialDataDAO
from src.models.stock import (
    StockBasicInfo, StockDailyData, StockFinancialData,
    StockHolderData, StockDividendData, StockMarginData,
    StockMinuteData, StockTickData
)


class StockBasicInfoDAO(FinancialDataDAO[StockBasicInfo]):
    """股票基本信息DAO"""
    
    async def get_active_stocks(self) -> List[StockBasicInfo]:
        """获取所有活跃股票"""
        result = await self.session.execute(
            select(StockBasicInfo).where(StockBasicInfo.is_active == True)
        )
        return list(result.scalars().all())
    
    async def get_by_market(self, market: str) -> List[StockBasicInfo]:
        """根据市场获取股票"""
        result = await self.session.execute(
            select(StockBasicInfo).where(StockBasicInfo.market == market)
        )
        return list(result.scalars().all())
    
    async def search_by_name(self, name_pattern: str) -> List[StockBasicInfo]:
        """根据股票名称模糊搜索"""
        result = await self.session.execute(
            select(StockBasicInfo).where(
                or_(
                    StockBasicInfo.name.ilike(f'%{name_pattern}%'),
                    StockBasicInfo.symbol.ilike(f'%{name_pattern}%')
                )
            )
        )
        return list(result.scalars().all())


class StockDailyDataDAO(FinancialDataDAO[StockDailyData]):
    """股票日线数据DAO"""
    
    async def get_price_history(
        self,
        symbol: str,
        start_date: date,
        end_date: date
    ) -> List[StockDailyData]:
        """获取股票价格历史数据"""
        result = await self.session.execute(
            select(StockDailyData)
            .where(
                and_(
                    StockDailyData.symbol == symbol,
                    StockDailyData.trade_date >= start_date,
                    StockDailyData.trade_date <= end_date
                )
            )
            .order_by(StockDailyData.trade_date)
        )
        return list(result.scalars().all())
    
    async def get_latest_price(self, symbol: str) -> Optional[StockDailyData]:
        """获取最新价格数据"""
        result = await self.session.execute(
            select(StockDailyData)
            .where(StockDailyData.symbol == symbol)
            .order_by(desc(StockDailyData.trade_date))
            .limit(1)
        )
        return result.scalar_one_or_none()
    
    async def get_high_volume_stocks(
        self,
        trade_date: date,
        min_volume: int = 1000000
    ) -> List[StockDailyData]:
        """获取高成交量股票"""
        result = await self.session.execute(
            select(StockDailyData)
            .where(
                and_(
                    StockDailyData.trade_date == trade_date,
                    StockDailyData.volume >= min_volume
                )
            )
            .order_by(desc(StockDailyData.volume))
        )
        return list(result.scalars().all())
    
    async def get_price_change_ranking(
        self,
        trade_date: date,
        limit: int = 100,
        ascending: bool = False
    ) -> List[StockDailyData]:
        """获取涨跌幅排行"""
        order_field = asc(StockDailyData.pct_change) if ascending else desc(StockDailyData.pct_change)
        result = await self.session.execute(
            select(StockDailyData)
            .where(StockDailyData.trade_date == trade_date)
            .order_by(order_field)
            .limit(limit)
        )
        return list(result.scalars().all())


class StockFinancialDataDAO(FinancialDataDAO[StockFinancialData]):
    """股票财务数据DAO"""
    
    async def get_latest_financial(self, symbol: str) -> Optional[StockFinancialData]:
        """获取最新财务数据"""
        result = await self.session.execute(
            select(StockFinancialData)
            .where(StockFinancialData.symbol == symbol)
            .order_by(desc(StockFinancialData.report_date))
            .limit(1)
        )
        return result.scalar_one_or_none()
    
    async def get_financial_history(
        self,
        symbol: str,
        years: int = 5
    ) -> List[StockFinancialData]:
        """获取历史财务数据"""
        result = await self.session.execute(
            select(StockFinancialData)
            .where(StockFinancialData.symbol == symbol)
            .order_by(desc(StockFinancialData.report_date))
            .limit(years * 4)  # 假设每年4个季度
        )
        return list(result.scalars().all())
    
    async def get_high_roe_stocks(
        self,
        min_roe: float = 0.15,
        report_date: Optional[date] = None
    ) -> List[StockFinancialData]:
        """获取高ROE股票"""
        conditions = [StockFinancialData.roe >= min_roe]
        if report_date:
            conditions.append(StockFinancialData.report_date == report_date)
        
        result = await self.session.execute(
            select(StockFinancialData)
            .where(and_(*conditions))
            .order_by(desc(StockFinancialData.roe))
        )
        return list(result.scalars().all())


class StockMinuteDataDAO(FinancialDataDAO[StockMinuteData]):
    """股票分钟数据DAO"""
    
    async def get_minute_data(
        self,
        symbol: str,
        start_time: datetime,
        end_time: datetime
    ) -> List[StockMinuteData]:
        """获取分钟级数据"""
        result = await self.session.execute(
            select(StockMinuteData)
            .where(
                and_(
                    StockMinuteData.symbol == symbol,
                    StockMinuteData.trade_time >= start_time,
                    StockMinuteData.trade_time <= end_time
                )
            )
            .order_by(StockMinuteData.trade_time)
        )
        return list(result.scalars().all())
    
    async def get_latest_minute_data(self, symbol: str) -> Optional[StockMinuteData]:
        """获取最新分钟数据"""
        result = await self.session.execute(
            select(StockMinuteData)
            .where(StockMinuteData.symbol == symbol)
            .order_by(desc(StockMinuteData.trade_time))
            .limit(1)
        )
        return result.scalar_one_or_none()


class StockTickDataDAO(FinancialDataDAO[StockTickData]):
    """股票Tick数据DAO"""
    
    async def get_tick_data(
        self,
        symbol: str,
        start_time: datetime,
        end_time: datetime
    ) -> List[StockTickData]:
        """获取Tick数据"""
        result = await self.session.execute(
            select(StockTickData)
            .where(
                and_(
                    StockTickData.symbol == symbol,
                    StockTickData.trade_time >= start_time,
                    StockTickData.trade_time <= end_time
                )
            )
            .order_by(StockTickData.trade_time)
        )
        return list(result.scalars().all())
    
    async def get_large_tick_trades(
        self,
        symbol: str,
        trade_date: date,
        min_volume: int = 10000
    ) -> List[StockTickData]:
        """获取大额Tick交易"""
        start_time = datetime.combine(trade_date, datetime.min.time())
        end_time = datetime.combine(trade_date, datetime.max.time())
        
        result = await self.session.execute(
            select(StockTickData)
            .where(
                and_(
                    StockTickData.symbol == symbol,
                    StockTickData.trade_time >= start_time,
                    StockTickData.trade_time <= end_time,
                    StockTickData.volume >= min_volume
                )
            )
            .order_by(desc(StockTickData.volume))
        )
        return list(result.scalars().all())


class StockDividendDataDAO(FinancialDataDAO[StockDividendData]):
    """股票分红数据DAO"""
    
    async def get_dividend_history(
        self,
        symbol: str,
        years: int = 5
    ) -> List[StockDividendData]:
        """获取分红历史"""
        result = await self.session.execute(
            select(StockDividendData)
            .where(StockDividendData.symbol == symbol)
            .order_by(desc(StockDividendData.ex_dividend_date))
            .limit(years * 2)  # 假设每年最多2次分红
        )
        return list(result.scalars().all())
    
    async def get_high_dividend_stocks(
        self,
        min_dividend_yield: float = 0.05,
        year: Optional[int] = None
    ) -> List[StockDividendData]:
        """获取高股息股票"""
        conditions = [StockDividendData.dividend_yield >= min_dividend_yield]
        if year:
            conditions.append(func.extract('year', StockDividendData.ex_dividend_date) == year)
        
        result = await self.session.execute(
            select(StockDividendData)
            .where(and_(*conditions))
            .order_by(desc(StockDividendData.dividend_yield))
        )
        return list(result.scalars().all())


class StockMarginDataDAO(FinancialDataDAO[StockMarginData]):
    """股票融资融券数据DAO"""
    
    async def get_margin_data(
        self,
        symbol: str,
        start_date: date,
        end_date: date
    ) -> List[StockMarginData]:
        """获取融资融券数据"""
        result = await self.session.execute(
            select(StockMarginData)
            .where(
                and_(
                    StockMarginData.symbol == symbol,
                    StockMarginData.trade_date >= start_date,
                    StockMarginData.trade_date <= end_date
                )
            )
            .order_by(StockMarginData.trade_date)
        )
        return list(result.scalars().all())
    
    async def get_high_margin_stocks(
        self,
        trade_date: date,
        min_margin_balance: float = 100000000  # 1亿
    ) -> List[StockMarginData]:
        """获取高融资余额股票"""
        result = await self.session.execute(
            select(StockMarginData)
            .where(
                and_(
                    StockMarginData.trade_date == trade_date,
                    StockMarginData.margin_balance >= min_margin_balance
                )
            )
            .order_by(desc(StockMarginData.margin_balance))
        )
        return list(result.scalars().all())


class StockHolderDataDAO(FinancialDataDAO[StockHolderData]):
    """股票股东数据DAO"""
    
    async def get_latest_holders(self, symbol: str) -> List[StockHolderData]:
        """获取最新股东数据"""
        # 先获取最新报告期
        latest_date_result = await self.session.execute(
            select(func.max(StockHolderData.report_date))
            .where(StockHolderData.symbol == symbol)
        )
        latest_date = latest_date_result.scalar()
        
        if not latest_date:
            return []
        
        result = await self.session.execute(
            select(StockHolderData)
            .where(
                and_(
                    StockHolderData.symbol == symbol,
                    StockHolderData.report_date == latest_date
                )
            )
            .order_by(desc(StockHolderData.holding_ratio))
        )
        return list(result.scalars().all())
    
    async def get_institutional_holders(
        self,
        symbol: str,
        report_date: Optional[date] = None
    ) -> List[StockHolderData]:
        """获取机构股东"""
        conditions = [
            StockHolderData.symbol == symbol,
            StockHolderData.holder_type.in_(['基金', '保险', '券商', '信托', 'QFII'])
        ]
        
        if report_date:
            conditions.append(StockHolderData.report_date == report_date)
        
        result = await self.session.execute(
            select(StockHolderData)
            .where(and_(*conditions))
            .order_by(desc(StockHolderData.holding_ratio))
        )
        return list(result.scalars().all())

